-- @owner: opentestcase041
-- @date: 2022-02-15
-- @testpoint: 账本数据库基础语法适配(ddl/dml)--对用户历史表和全局区块表创建函数，调用函数时合理报错

--step1: 查询三权分立参数enableseparationofduty; expect:显示默认值off
show enableseparationofduty;

--step2: 创建不同权限用户; expect:用户创建成功
drop user if exists u_security_tamper_proof_sys_0006;
drop user if exists u_security_tamper_proof_noruser_0006;
create user u_security_tamper_proof_sys_0006 with sysadmin password 'test@123';
create user u_security_tamper_proof_noruser_0006 password 'test@123';

--step3: 系统管理员创建schema并指定防篡改选项; expect:成功
set role u_security_tamper_proof_sys_0006 password 'test@123';
select current_user;
drop schema if exists s_security_tamper_proof_0006;
create schema s_security_tamper_proof_0006 authorization u_security_tamper_proof_noruser_0006 with blockchain;

--step4: 普通用户在防篡改schema下创建防篡改用户表; expect:成功
set role u_security_tamper_proof_noruser_0006 password 'test@123';
select current_user;
drop table if exists s_security_tamper_proof_0006.t_security_tamper_proof_0006;
create table s_security_tamper_proof_0006.t_security_tamper_proof_0006(id int primary key ,name varchar(20) not null ,address text default 'shanxi');

--step5: 重置用户; expect:成功
reset role;
select current_user;

--step6: 对用户历史表创建函数; expect:成功
create or replace function f_security_tamper_proof_0006_func_01() returns void as $$
begin
insert into blockchain.s_security_tamper_proof_0006_t_security_tamper_proof_0006_hist values(2,'f2d6782393ad2f1f',null,'c7b12e7bc2915832a72bc46b5cbe6990');
end;
$$ language plpgsql;
/
--step7: 对全局区块表创建函数; expect:成功
create or replace function f_security_tamper_proof_0006_func_02() returns void as $$
begin
insert into gs_global_chain values(2,'postgres','u_security_tamper_proof_noruser_0006','2021-04-20 23:51:58.320212+08','16484','s_security_tamper_proof_0006','t_security_tamper_proof_0006','83f570c6d3e7372c','355aad5bd89a753ef3090d8ce72a253c','copy t_security_tamper_proof_0006 (id, name, address, hash) from stdin;');
end;
$$ language plpgsql;
/
--step8: 调用函数; expect:合理报错
call f_security_tamper_proof_0006_func_01();
call f_security_tamper_proof_0006_func_02();

--step9: 清理环境; expect:成功
drop table s_security_tamper_proof_0006.t_security_tamper_proof_0006 cascade;
drop schema s_security_tamper_proof_0006 cascade;
drop user u_security_tamper_proof_sys_0006 cascade;
drop user u_security_tamper_proof_noruser_0006 cascade;
drop function f_security_tamper_proof_0006_func_01;
drop function f_security_tamper_proof_0006_func_02;